﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;

namespace TurtleEngine
{
	 /// <summary>
	 ///
	 /// </summary>
	 public class TablesHelper
	 {
		  /// <summary>
		  /// The columns description
		  /// </summary>
		  private static DataTable columnsDescription = new DataTable("ColumnsDescription");

		  /// <summary>
		  /// The schemas
		  /// </summary>
		  private static DataTable schemas = new DataTable("Schema");

		  /// <summary>
		  /// The table description
		  /// </summary>
		  private static DataTable tableDescription = new DataTable("TableDescription");

		  /// <summary>
		  /// The tables
		  /// </summary>
		  private static DataTable tables = new DataTable("Tables");

		  /// <summary>
		  /// Gets the columns description.
		  /// </summary>
		  /// <value>
		  /// The columns description.
		  /// </value>
		  public static DataTable ColumnsDescription
		  {
				get { return columnsDescription; }
		  }

		  /// <summary>
		  /// Gets the table description.
		  /// </summary>
		  /// <value>
		  /// The table description.
		  /// </value>
		  public static DataTable TableDescription
		  {
				get { return tableDescription; }
		  }

		  /// <summary>
		  /// Gets the tables.
		  /// </summary>
		  /// <value>
		  /// The tables.
		  /// </value>
		  public static DataTable Tables
		  {
				get { return tables; }
		  }

		  /// <summary>
		  /// Gets the tables.
		  /// </summary>
		  public static void GetTables()
		  {
				var restrictions = new string[4];
				restrictions[0] = null; //database/catalog name
				restrictions[1] = null; //owner/schema name
				restrictions[2] = null; //table name

				// commented as we need both tables + views
				//restrictions[3] = "BASE TABLE";   //table type

				CleanUp();

				tables = Utility.DBConnection.GetSchema("Tables", restrictions);

				//Utility.PrintDatatable(tables);

				GetSchemas();
				GetTableDescription();
				GetColumnsDescription();
				GetTablesSpaceUsed();
				GetTableColumnCount();
				GetTablesScript(Utility.DatabaseName);
		  }

		  /// <summary>
		  /// Writes the tables.
		  /// </summary>
		  public static void WriteTables()
		  {
				WriteTableList();
				WriteTableDetails();
		  }

		  /// <summary>
		  /// Adds the table space used columns.
		  /// </summary>
		  private static void AddTableSpaceUsedColumns()
		  {
				tables.Columns.Add("TABLE_ROWS", typeof(String));
				tables.Columns.Add("TABLE_RESERVED", typeof(String));
				tables.Columns.Add("TABLE_DATA", typeof(String));
				tables.Columns.Add("TABLE_INDEX_SIZE", typeof(String));
				tables.Columns.Add("TABLE_UNUSED", typeof(String));
		  }

		  /// <summary>
		  /// Cleans up.
		  /// </summary>
		  private static void CleanUp()
		  {
				if (tables != null)
					 tables.Clear();

				if (schemas != null)
					 schemas.Clear();

				if (tableDescription != null)
					 tableDescription.Clear();

				if (columnsDescription != null)
					 columnsDescription.Clear();
		  }

		  /// <summary>
		  /// Gets the columns description.
		  /// </summary>
		  private static void GetColumnsDescription()
		  {
				string query = string.Empty;
				SqlCommand cmd;
				SqlDataAdapter adapter;
				var ds = new DataSet("columnsDescription");

				foreach (DataRow schemaRow in schemas.Rows)
				{
					 foreach (DataRow tableRow in tables.Rows)
					 {
						  query = "SELECT OBJTYPE, OBJNAME, NAME, VALUE, '" + tableRow["TABLE_NAME"] + "' AS TABLE_NAME " +
								  "FROM fn_listextendedproperty ('MS_Description', 'schema', '" + schemaRow["SCHEMA_NAME"] + "', 'table', '" +
								  tableRow["TABLE_NAME"] + "', 'column', default);";

						  cmd = new SqlCommand(query, Utility.DBConnection);
						  adapter = new SqlDataAdapter(query, Utility.DBConnection);
						  adapter.Fill(ds, "columnsDescription");
					 }
				}
				columnsDescription = ds.Tables["columnsDescription"];
				JoinColumnsDescription();
		  }

		  /// <summary>
		  /// Gets the schemas.
		  /// </summary>
		  private static void GetSchemas()
		  {
				String query = "SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER, DEFAULT_CHARACTER_SET_CATALOG, " +
								"DEFAULT_CHARACTER_SET_SCHEMA, DEFAULT_CHARACTER_SET_NAME " +
								"FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_OWNER='dbo'";

				var cmd = new SqlCommand(query, Utility.DBConnection);
				var adapter = new SqlDataAdapter(query, Utility.DBConnection);

				var ds = new DataSet();
				adapter.Fill(ds, "schemas");
				schemas = ds.Tables["schemas"];
		  }

		  /// <summary>
		  /// Gets the SMO meta data.
		  /// </summary>
		  /// <param name="database">The database.</param>
		  private static void GetSMOMetaData(string database)
		  {
				//tables.Columns.Add("TABLE_SCRIPT", typeof(String));

				var serverConnection = new ServerConnection(Utility.DBConnection);
				var server = new Server(serverConnection);
				Database db = server.Databases[database];

				var so = new ScriptingOptions();
				so.ChangeTracking = true;
				so.ClusteredIndexes = true;
				so.ExtendedProperties = true;

				foreach (Table table in db.Tables)
				{
					 StringCollection script = table.Script(so);
					 var scriptArray = new string[script.Count];
					 script.CopyTo(scriptArray, 0);

					 DataRow tableRow = (tables.Select("TABLE_NAME = '" + table.Name + "'"))[0];
					 for (int i = 0; i < scriptArray.Length; i++)

						  //scriptArray[i] = (scriptArray[i].Length > 150) ? Utility.SplitString(scriptArray[i], 150) :scriptArray[i];
						  scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");

					 tableRow["TABLE_SCRIPT"] = string.Join(Environment.NewLine, scriptArray);
				}
		  }

		  /// <summary>
		  /// Gets the table column count.
		  /// </summary>
		  private static void GetTableColumnCount()
		  {
				var ht = new Hashtable(20);
				Int32 columnCount = 0;
				foreach (DataRow row in tables.Rows)
				{
					 columnCount = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'")).Length;
					 ht.Add(row["TABLE_NAME"].ToString(), columnCount);
				}
				JoinTableColumnCount(ht);
		  }

		  /// <summary>
		  /// Gets the table description.
		  /// </summary>
		  private static void GetTableDescription()
		  {
				string query = string.Empty;
				SqlCommand cmd;
				SqlDataAdapter adapter;
				var ds = new DataSet("tableDescription");

				foreach (DataRow row in schemas.Rows)
				{
					 query = "SELECT OBJTYPE, OBJNAME, NAME, VALUE " +
							 "FROM fn_listextendedproperty ('MS_Description', 'schema', '" + row["SCHEMA_NAME"] +
							 "', 'table', default, null, null) " +
							 "UNION " +
							 "SELECT OBJTYPE, OBJNAME, NAME, VALUE " +
							 "FROM fn_listextendedproperty ('MS_Description', 'schema', '" + row["SCHEMA_NAME"] +
							 "', 'view', default, null, null); ";

					 cmd = new SqlCommand(query, Utility.DBConnection);
					 adapter = new SqlDataAdapter(query, Utility.DBConnection);
					 adapter.Fill(ds, "tableDescription");
				}
				tableDescription = ds.Tables["tableDescription"];
				JoinTableDescription();
		  }

		  /// <summary>
		  /// Gets the tables script.
		  /// </summary>
		  /// <param name="database">The database.</param>
		  private static void GetTablesScript(string database)
		  {
				tables.Columns.Add("TABLE_SCRIPT", typeof(String));

				var serverConnection = new ServerConnection(Utility.DBConnection);
				var server = new Server(serverConnection);
				Database db = server.Databases[database];

				var so = new ScriptingOptions();
				so.ChangeTracking = true;
				so.ClusteredIndexes = true;
				so.ExtendedProperties = true;

				foreach (Table table in db.Tables)
				{
					 StringCollection script = table.Script(so);
					 var scriptArray = new string[script.Count];
					 script.CopyTo(scriptArray, 0);

					 DataRow tableRow = (tables.Select("TABLE_NAME = '" + table.Name + "'"))[0];
					 for (int i = 0; i < scriptArray.Length; i++)

						  //scriptArray[i] = (scriptArray[i].Length > 150) ? Utility.SplitString(scriptArray[i], 150) :scriptArray[i];
						  scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");

					 tableRow["TABLE_SCRIPT"] = string.Join(Environment.NewLine, scriptArray);
				}
		  }

		  /// <summary>
		  /// Gets the tables space used.
		  /// </summary>
		  private static void GetTablesSpaceUsed()
		  {
				string query = string.Empty;
				SqlCommand cmd;
				SqlDataAdapter adapter;
				var ds = new DataSet("SpaceUsed");
				DataTable spaceUsed;

				foreach (DataRow row in tables.Rows)
				{
					 query = "sp_spaceused N'" + row["TABLE_SCHEMA"] + "." + row["TABLE_NAME"] + "'";

					 try
					 {
						  cmd = new SqlCommand(query, Utility.DBConnection);
						  adapter = new SqlDataAdapter(query, Utility.DBConnection);
						  adapter.Fill(ds, "SpaceUsed");
					 }
					 catch (Exception ex)
					 {
						  Debug.WriteLine(ex.Message);
					 }
				}

				spaceUsed = ds.Tables["SpaceUsed"];
				AddTableSpaceUsedColumns();
				if (spaceUsed != null)
					 JoinTableSpaceUsed(spaceUsed);
		  }

		  /// <summary>
		  /// Joins the columns description.
		  /// </summary>
		  private static void JoinColumnsDescription()
		  {
				ColumnsHelper.Columns.Columns.Add("COLUMN_DESCRIPTION", typeof(String));

				//Utility.PrintDatatable(columnsDescription);

				foreach (DataRow row in ColumnsHelper.Columns.Rows)
				{
					 DataRow[] rows =
						 (columnsDescription.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "' AND OBJNAME = '" + row["COLUMN_NAME"] + "'"));
					 if (rows.Length > 0)
						  row["COLUMN_DESCRIPTION"] = rows[0]["VALUE"];
				}
		  }

		  /// <summary>
		  /// Joins the table column count.
		  /// </summary>
		  /// <param name="ht">The ht.</param>
		  private static void JoinTableColumnCount(Hashtable ht)
		  {
				tables.Columns.Add("TABLE_COLUMNSCOUNT", typeof(String));

				foreach (DataRow row in tables.Rows)
				{
					 row["TABLE_COLUMNSCOUNT"] = ht[row["TABLE_NAME"].ToString()];
				}
		  }

		  /// <summary>
		  /// Joins the table description.
		  /// </summary>
		  private static void JoinTableDescription()
		  {
				tables.Columns.Add("TABLE_DESCRIPTION", typeof(String));

				foreach (DataRow row in tables.Rows)
				{
					 DataRow[] rows = (tableDescription.Select("OBJNAME = '" + row["TABLE_NAME"] + "'"));

					 if (rows.Length > 0)
						  row["TABLE_DESCRIPTION"] = rows[0]["VALUE"];
				}
		  }

		  /// <summary>
		  /// Joins the table space used.
		  /// </summary>
		  /// <param name="dt">The dt.</param>
		  private static void JoinTableSpaceUsed(DataTable dt)
		  {
				DataRow dr;
				foreach (DataRow row in tables.Rows)
				{
					 DataRow[] rows = (dt.Select("name = '" + row["TABLE_NAME"] + "'"));
					 if (rows.Length > 0)
					 {
						  dr = rows[0];
						  row["TABLE_ROWS"] = dr["rows"].ToString();
						  row["TABLE_RESERVED"] = dr["reserved"].ToString();
						  row["TABLE_DATA"] = dr["data"].ToString();
						  row["TABLE_INDEX_SIZE"] = dr["index_size"].ToString();
						  row["TABLE_UNUSED"] = dr["unused"].ToString();
					 }
				}
		  }

		  /// <summary>
		  /// Writes the table details.
		  /// </summary>
		  private static void WriteTableDetails()
		  {
				string xmlfile = string.Empty;
				string xslFile = string.Empty;
				string htmFile = string.Empty;

				var ds = new DataSet();

				#region "Define DataTable for TableProperties"

				var tableProperties = new DataTable("TableProperties");
				tableProperties.Columns.Add("NAME", typeof(String));
				tableProperties.Columns.Add("VALUE", typeof(String));
				ds.Tables.Add(tableProperties);

				#endregion "Define DataTable for TableProperties"

				#region "Define DataTable for Columns"

				DataTable tableColumns = ColumnsHelper.Columns.Clone();
				ds.Tables.Add(tableColumns);

				#endregion "Define DataTable for Columns"

				#region "Define DataTable for Indexes"

				DataTable tableIndexes = IndexesHelper.Indexes.Clone();
				ds.Tables.Add(tableIndexes);

				#endregion "Define DataTable for Indexes"

				#region "Define DataTable for Indexes"

				DataTable foreignKeys = ForeignKeysHelper.ForeignKeys.Clone();
				ds.Tables.Add(foreignKeys);

				#endregion "Define DataTable for Indexes"

				#region "Define DataTable for SQL"

				var tableSQL = new DataTable("SQL");
				tableSQL.Columns.Add("CreationScript", typeof(String));
				ds.Tables.Add(tableSQL);

				#endregion "Define DataTable for SQL"

				foreach (DataRow row in tables.Rows)
				{
					 if (row["TABLE_TYPE"].ToString().Equals("BASE TABLE"))
					 {
						  string tableName = row["TABLE_NAME"].ToString();
						  xmlfile = tableName + ".xml";
						  xslFile = "TableDetails.xsl";
						  htmFile = tableName + ".htm";

						  #region "Fill TableProperties"

						  DataRow dr = tableProperties.NewRow();
						  dr["NAME"] = "Description";
						  dr["VALUE"] = row["TABLE_DESCRIPTION"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Name";
						  dr["VALUE"] = tableName;
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Schema";
						  dr["VALUE"] = row["TABLE_SCHEMA"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Data Size";
						  dr["VALUE"] = row["TABLE_DATA"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Index Size";
						  dr["VALUE"] = row["TABLE_INDEX_SIZE"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Rows";
						  dr["VALUE"] = row["TABLE_ROWS"].ToString();
						  tableProperties.Rows.Add(dr);

						  dr = tableProperties.NewRow();
						  dr["NAME"] = "Columns";
						  dr["VALUE"] = row["TABLE_COLUMNSCOUNT"].ToString();
						  tableProperties.Rows.Add(dr);

						  #endregion "Fill TableProperties"

						  #region "Fill Columns"

						  DataRow[] colRows = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
						  foreach (DataRow tempDr in colRows)
						  {
								tableColumns.ImportRow(tempDr);
						  }

						  #endregion "Fill Columns"

						  #region "Fill Indexes"

						  DataRow[] indexRows = (IndexesHelper.Indexes.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
						  foreach (DataRow tempDr in indexRows)
						  {
								tableIndexes.ImportRow(tempDr);
						  }

						  #endregion "Fill Indexes"

						  #region "Fill Indexes"

						  DataRow[] foreignKeyRows = (ForeignKeysHelper.ForeignKeys.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
						  foreach (DataRow tempDr in foreignKeyRows)
						  {
								foreignKeys.ImportRow(tempDr);
						  }

						  #endregion "Fill Indexes"

						  #region "SQL"

						  DataRow[] sqlRows = (tables.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
						  foreach (DataRow tempDr in sqlRows)
						  {
								DataRow sql = tableSQL.NewRow();
								sql["CreationScript"] = tempDr["TABLE_SCRIPT"];
								tableSQL.Rows.Add(sql);
						  }

						  #endregion "SQL"

						  File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
						  var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
						  ht.TableTransformer();

						  tableProperties.Clear();
						  tableColumns.Clear();
						  tableIndexes.Clear();
						  foreignKeys.Clear();
						  tableSQL.Clear();
					 }
				}
		  }

		  /// <summary>
		  /// Writes the table list.
		  /// </summary>
		  private static void WriteTableList()
		  {
				string xmlfile = "TableList.xml";
				string xslFile = "TableList.xsl";
				string htmFile = "TableList.htm";

				var ds = new DataSet("TableList");
				var dv = new DataView(tables);
				dv.RowFilter = "TABLE_TYPE = 'BASE TABLE'";
				ds.Tables.Add(dv.ToTable());

				File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());

				var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
				ht.TableTransformer();
		  }
	 }
}